﻿using FastData;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sample
{
    class Program
    {
        static void Main(string[] args)
        {
            //first create database 'FastDataSample', execute script.sql and modify connectionstring in app.config 
            using (var data = new DataAccess("SqlSample"))
            {
                RunSample(data);
                Console.WriteLine("SqlSample Finished.\n");
            }

            //FastDataSample.sdf
            using (var data = new DataAccess("SqlCeSample"))
            {
                RunSample(data);
                Console.WriteLine("SqlCeSample Finished.\n");
            }

            Console.Read();
        }


        static void RunSample(DataAccess data)
        {
            InsertCustomers1(data);
            InsertCustomers2(data); //lazy
            InsertCustomers3(data);
            InsertCustomers4(data); //executeId

            SelectCustomers1(data);
            SelectCustomers2(data);
            SelectCustomers3(data);
            SelectCustomers4(data); //cache
            SelectCustomers5(data).Wait(); //async
            SelectCustomers6(data);
            SelectCustomers7(data); //join

            //close connection for long running tasks. automatic reconnect when execute next query.
            data.Close();

            //...
            //long running tasks
            //...

            UpdateCustomers1(data);
            UpdateCustomers2(data);
            UpdateCustomers3(data);

            DeleteCustomers1(data);
            DeleteCustomers2(data);
            DeleteCustomers3(data);

            ExecQuery(data);
        }


        static void InsertCustomers1(DataAccess data)
        {
            for (int i = 0; i < 5; i++)
            {
                var customer = new Customer()
                {
                    FirstName = "customer" + i,
                    Address = "address" + i
                };
                data.Insert<Customer>(customer).Execute();

                Console.WriteLine("insert " + 1 + " Customer");
            }
        }


        static void InsertCustomers2(DataAccess data)
        {
            for (int i = 10; i < 20; i++)
            {
                var customer = new Customer()
                {
                    FirstName = "customer" + i,
                    Address = "address" + i
                };
                data.Insert<Customer>(customer).ExecuteLazy();
            }

            int result = data.ExecuteLazyQueries();

            Console.WriteLine("insert " + result + " Customer");
        }


        static void InsertCustomers3(DataAccess data)
        {
            for (int i = 20; i < 30; i++)
            {
                data.Insert("Customer")
                    .Field("FirstName", "customer" + i)
                    .Field("Address", "address" + i)
                    .ExecuteLazy();
            }

            int result = data.ExecuteLazyQueries();

            Console.WriteLine("insert " + result + " Customer");
        }


        static void InsertCustomers4(DataAccess data)
        {
            var customerId = data.Insert("Customer")
                                 .Field("FirstName", "test")
                                 .Field("Address", "test...")
                                 .ExecuteId();

            data.Insert("Order")
                .Field("Date", DateTime.Now)
                .Field("CustomerId", customerId)
                .Execute();

            Console.WriteLine("insert 1 Customer and 1 Order");
        }


        static void SelectCustomers1(DataAccess data)
        {
            var cutomers = data.Select<Customer>().ToList();

            Console.WriteLine("select " + cutomers.Count + " Customer");
        }


        static void SelectCustomers2(DataAccess data)
        {
            var cutomers = data.Select("Id", "FirstName", "Address")
                                .From("Customer")
                                .Execute<Customer>();

            Console.WriteLine("select " + cutomers.Count() + " Customer");
        }


        static void SelectCustomers3(DataAccess data)
        {
            var cutomers = data.Select("Id", "FirstName", "Address")
                                .From("Customer")
                                .ExecuteDataTable();

            Console.WriteLine("select " + cutomers.Rows.Count + " Customer");
        }


        static void SelectCustomers4(DataAccess data)
        {
            var cutomers = data.Select("Id", "FirstName", "Address")
                                .From("Customer")
                                .Cache(TimeSpan.FromMinutes(10))
                                .ExecuteDataTable();


            //read from cache
            cutomers = data.Select("Id", "FirstName", "Address")
                                .From("Customer")
                                .Cache(TimeSpan.FromMinutes(10))
                                .ExecuteDataTable();

            Console.WriteLine("select " + cutomers.Rows.Count + " Customer");
        }


        static async Task SelectCustomers5(DataAccess data)
        {
            var cutomers = await data.Select("Id", "FirstName", "Address")
                                .From("Customer")
                                .ExecuteDataTableAsync();

            Console.WriteLine("select " + cutomers.Rows.Count + " Customer");
        }


        static void SelectCustomers6(DataAccess data)
        {
            var cutomers = data.Query("select Id, FirstName, Address from Customer").Execute<Customer>();

            Console.WriteLine("select " + cutomers.Count() + " Customer");
        }


        static void SelectCustomers7(DataAccess data)
        {
            var cutomers = data.Select("Order.Id", "Date", "FirstName", "Address")
                                .From("Order")
                                .InnerJoin("Customer", "Order.CustomerId", "Customer.Id")
                                .ExecuteDataTable();

            Console.WriteLine("select " + cutomers.Rows.Count + " Customer");
        }

        static void UpdateCustomers1(DataAccess data)
        {
            var customer = new Customer()
            {
                Id = 1,
                FirstName = "customer (updated)",
                Address = "address (updated)"
            };
            data.Update<Customer>(customer).ExecuteLazy();

            Console.WriteLine("update " + 1 + " Customer");
        }


        static void UpdateCustomers2(DataAccess data)
        {
            data.Update<Customer>()
                .Set(c => c.FirstName, "customer (updated)")
                .Where(c => c.Id == 1)
                .ExecuteLazy();

            Console.WriteLine("update " + 1 + " Customer");
        }


        static void UpdateCustomers3(DataAccess data)
        {
            data.Update("Customer")
                .Set("FirstName", "customer (updated)")
                .Set("Address", "address (updated)")
                .Where("Id", 1)
                .Execute();

            Console.WriteLine("update " + 1 + " Customer");
        }


        static void DeleteCustomers1(DataAccess data)
        {
            var customer = new Customer()
            {
                Id = 1
            };
            data.Delete<Customer>(customer).Execute();

            Console.WriteLine("delete " + 1 + " Customer");
        }


        static void DeleteCustomers2(DataAccess data)
        {
            data.Delete<Customer>()
                .Where(c => c.Id == 1)
                .Execute();

            Console.WriteLine("delete " + 1 + " Customer");
        }


        static void DeleteCustomers3(DataAccess data)
        {
            data.Delete("Customer")
                .Where("Id", 1)
                .Execute();

            Console.WriteLine("delete " + 1 + " Customer");
        }

        static void ExecQuery(DataAccess data)
        {
            data.Query("update Customer set address=null").Execute();

            Console.WriteLine("Query Executed");
        }
    }
}
